package com.jkl.untils.excel;

import com.google.common.collect.Lists;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.StringUtils;

import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLDecoder;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
//import sun.misc.BASE64Decoder;

/**
 * @Auther: zhukangkang
 * @Date: 2019/09/23
 * @Description: excel 工具使用类
 */
public class ExcelUtil {
    private static volatile ExcelUtil excelUtil;

    private ExcelUtil(){}

    public static ExcelUtil getInstance(){
        if(excelUtil == null){
            synchronized (ExcelUtil.class){
                if(excelUtil == null){
                    excelUtil = new ExcelUtil();
                }
            }
        }
        return excelUtil;
    }


    public void exportExcel(List<?> dataList, Class clazz,String excelTitle,HttpServletResponse response,String fileName) throws IOException {
        response.setContentType("application/OCTET-STREAM;charset=UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName+".xlsx", "UTF-8"));
        exportExcel(dataList, clazz, excelTitle,response.getOutputStream());
    }

    public void exportExcel(List<?> dataList, Class clazz, String excelTitle, OutputStream out)
            throws IOException {
        Workbook workbook = null;
        try {
            workbook = exportExcelNoTemplateHandler(dataList, clazz, excelTitle, null, true, null);
            workbook.write(out);
        } finally {
            if (workbook != null)
                workbook.close();
            if (out != null)
                out.close();
        }
    }

    public void exportTemplateExcel(Class clazz, String excelTitle, OutputStream out) throws IOException {
        Workbook workbook = null;
        try {
            workbook = exportExcelNoTemplateHandler(Lists.newArrayList(), clazz, null, excelTitle, true,null);
            workbook.write(out);
        } finally {
            if (workbook != null)
                workbook.close();
            if (out != null)
                out.close();
        }
    }


    public <T> List<T> readExcel2Objects(InputStream is, Class<T> clazz) throws IOException {
        try (Workbook workbook = WorkbookFactory.create(is)) {
            return readExcel2ObjectsHandler(workbook, clazz, 0, 1000, 0);
        }
    }

    public <T> List<T> readExcel2Objects(InputStream is, Class<T> clazz, int offsetLine,
                                         int limitLine, int sheetIndex) throws IOException {
        try (Workbook workbook = WorkbookFactory.create(is)) {
            return readExcel2ObjectsHandler(workbook, clazz, offsetLine, limitLine, sheetIndex);
        }
    }

    // sheet数据导出
    private Workbook exportExcelNoTemplateHandler(List<?> dataList, Class clazz, String title,
                                                  String sheetName, boolean isXSSF,List<ExcelHeader> headerList){
        Workbook workbook;
        if (isXSSF) {
            workbook = new XSSFWorkbook();
        } else {
            workbook = new HSSFWorkbook();
        }
        generateSheet(workbook, dataList, clazz, title, sheetName, headerList);

        return workbook;
    }

    // 生成sheet数据
    private void generateSheet(Workbook workbook, List<?> data, Class clazz, String title, String sheetName, List<ExcelHeader> headerList) {
        Sheet sheet;
        if (null != sheetName && !"".equals(sheetName)) {
            sheet = workbook.createSheet(sheetName);
        } else {
            sheet = workbook.createSheet();
        }

        Row row = sheet.createRow(1);
        row.setHeightInPoints(18);
        int headCount = 0;
        List<ExcelHeader> headers = new ArrayList<>();
        if(headerList != null && headerList.size() > 0) {
            headers = headerList;
            for (int i = 0; i < headerList.size(); i++) {
                Cell headCell = row.createCell(headCount);
                headCell.setCellValue(headerList.get(i).getTitle());
                CellStyle bodyStyle = setHeadCellStyle(workbook);
                headCell.setCellStyle(bodyStyle);
                sheet.setColumnWidth(headCount, 3840);
                headCount++;
            }
        }else {
            headers = ExcelOpUtils.getHeaderList(clazz);
            for (int i = 0; i < headers.size(); i++) {
                if(!headers.get(i).isExport() || headers.get(i).isPk()) continue;
                Cell headCell = row.createCell(headCount);
                headCell.setCellValue(headers.get(i).getTitle());
                CellStyle bodyStyle = setHeadCellStyle(workbook);
                headCell.setCellStyle(bodyStyle);
                sheet.setColumnWidth(headCount, headers.get(i).getWidth()*256);
                headCount++;
            }
        }

        if(headCount <=0) {
            return;
        }
        excelTitle(title, sheet, workbook, headCount-1);

        // 写数据
        Object _data;
        if(CommonUtil.isEmpty(data)) return;
        CellStyle bodyStyle = setCellStyle(workbook);
        for (int i = 0; i < data.size(); i++) {
            row = sheet.createRow(i + 2);
            row.setHeightInPoints(18);
            _data = data.get(i);
            int bodyCount = 0;
            for (int j = 0; j < headers.size(); j++) {
                ExcelHeader head = headers.get(j);
                String cellValue = "";
                if(headerList != null && headerList.size() > 0) {
                    cellValue = (String)((Map)_data).get(head.getTitle());
                }else {
                    if(!head.isExport() || head.isPk()) continue;
                    cellValue = ExcelOpUtils.getProperty(_data, head.getFiled());
                    if(CommonUtil.isNotEmpty(head.getDicKey())){
                        for(int k=0; k<head.getDicKey().length; k++){
                            String dicKey = head.getDicKey()[k];
                            if(CommonUtil.equals(cellValue, dicKey)){
                                cellValue = head.getDicValue()[k];
                                break;
                            }
                        }
                    }
                    if(head.isIncrease()){
                        cellValue = i+1+"";
                    }
                }
                Cell bodyCell = row.createCell(bodyCount);
                bodyCell.setCellValue(cellValue);
                bodyCell.setCellStyle(bodyStyle);
                bodyCount++;
            }
        }
    }

    private <T> List<T> readExcel2ObjectsHandler(Workbook workbook, Class<T> clazz, int offsetLine,
                                                 int limitLine, int sheetIndex) {
        if(sheetIndex<0){
            sheetIndex = 0;
        }
        Sheet sheet = workbook.getSheetAt(sheetIndex);
        int excelRowNum = sheet.getLastRowNum();
        if(limitLine < (excelRowNum - offsetLine)){
            throw new TipException("Excel 最大只能导入" + limitLine + "行数据");
        }
        Row row = sheet.getRow(offsetLine);
        List<T> list = new ArrayList<>();
        Map<Integer, ExcelHeader> maps = ExcelOpUtils.getImportHeaderMap(row, clazz);
        if (CommonUtil.isEmpty(maps))
            throw new TipException("表格格式错误，请确认所选模板是否正确。");
        ExcelHeader pkHeader = ExcelOpUtils.pkHeader(clazz);
        long maxLine = sheet.getLastRowNum() > ((long) offsetLine + limitLine) ?
                ((long) offsetLine + limitLine) : sheet.getLastRowNum();

        for (int i = offsetLine + 1; i <= maxLine; i++) {
            row = sheet.getRow(i);
            if (null == row)
                continue;
            T obj;
            try {
                obj = clazz.newInstance();
            } catch (InstantiationException | IllegalAccessException e) {
                throw new BaseException(e);
            }
            for (Cell cell : row) {
                int ci = cell.getColumnIndex();
                ExcelHeader header = maps.get(ci);
                if (header == null)
                    continue;

                String val = ExcelOpUtils.getCellValue(cell);
                String filed = header.getFiled();
                Object value = ExcelOpUtils.str2TargetClass(val, header.getFiledClazz());
                if(CommonUtil.isNotEmpty(header.getDicValue())){
                    for(int k=0; k<header.getDicValue().length; k++){
                        String dicValue = header.getDicValue()[k];
                        if(CommonUtil.equals(value, dicValue)){
                            value = header.getDicKey()[k];
                            break;
                        }
                    }
                }
                ExcelOpUtils.copyProperty(obj, filed, value);
            }
            String uuid = CommonUtil.createUUID();
            ExcelOpUtils.copyProperty(obj, pkHeader.getFiled(), uuid);
            list.add(obj);
        }
        return list;
    }

    private void excelTitle(String title, Sheet sheet, Workbook workbook, int rangeCount){
        Row row = sheet.createRow(0);
        row.setHeightInPoints(20);
        Cell titleCell = row.createCell(0);
        titleCell.setCellValue(title);

        CellRangeAddress region=new CellRangeAddress(0, 0, 0, rangeCount);
        CellStyle style=workbook.createCellStyle();
        Font fontStyle = workbook.createFont();
        fontStyle.setBold(true);
        fontStyle.setFontHeightInPoints((short) 12);
        style.setFont(fontStyle);
        style.setFillForegroundColor(IndexedColors.SKY_BLUE.index);
        style.setAlignment(HorizontalAlignment.CENTER);//水平居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        sheet.addMergedRegion(region);
        titleCell.setCellStyle(style);
        RegionUtil.setBorderBottom(BorderStyle.THIN, region, sheet); // 下边框
        RegionUtil.setBorderLeft(BorderStyle.THIN, region, sheet); // 左边框
        RegionUtil.setBorderRight(BorderStyle.THIN, region, sheet); // 有边框
        RegionUtil.setBorderTop(BorderStyle.THIN, region, sheet); // 上边框
    }

    private static CellStyle setHeadCellStyle(Workbook wb){
        CellStyle cellStyle = setCellStyle(wb);
        Font fontStyle = wb.createFont();
        fontStyle.setBold(true);
        cellStyle.setFont(fontStyle);
        cellStyle.setFillForegroundColor(IndexedColors.SKY_BLUE.index);
        // cellStyle.setFillPattern(FillPatternType.BIG_SPOTS);
        return cellStyle;
    }

    private static CellStyle setCellStyle(Workbook wb) {
        Font font = wb.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 10);
        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setFont(font);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setWrapText(false); // 自动换行
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
        return cellStyle;
    }

    public void exportExcel(List<?> dataList, Class clazz,String excelTitle,HttpServletResponse response,String fileName
            ,List<ExcelHeader> headerList) throws IOException {
        response.setContentType("application/OCTET-STREAM;charset=UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName+".xlsx", "UTF-8"));
        exportExcel(dataList, clazz, excelTitle,response.getOutputStream(),headerList);
    }

    public void exportExcel(List<?> dataList, Class clazz, String excelTitle, OutputStream out,List<ExcelHeader> headerList)
            throws IOException {
        Workbook workbook = null;
        try {
            workbook = exportExcelNoTemplateHandler(dataList, clazz, excelTitle, null, true, headerList);
            workbook.write(out);
        } finally {
            if (workbook != null)
                workbook.close();
            if (out != null)
                out.close();
        }
    }

    public  static void exportForEchartsImg(Map<String,String> map,
                                            HttpServletResponse response) throws Exception {

        //创建Excel工作簿
        HSSFWorkbook wb = new HSSFWorkbook();
        //创建sheet页
        HSSFSheet sheet = wb.createSheet("可视数据");
        //创建绘图(画布)，注明：一个sheet只能创建一个画布，但一个画布中可以添加多张图片
        HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
       if (!StringUtils.isEmpty(map.get("dataChartOne"))){
           String dataChartOne = map.get("dataChartOne");
           String dataChartCostStr = (URLDecoder.decode(dataChartOne.substring(22), "UTF-8")).replaceAll(" ", "+");
           //图表图片
           createPictureInExcel(dataChartCostStr, patriarch, wb, (short) 1, 1, (short) 22, 25);
       }
        if (!StringUtils.isEmpty(map.get("dataChartTwo"))){
            String dataChartTwo = map.get("dataChartTwo");
            String dataChartCostStr = (URLDecoder.decode(dataChartTwo.substring(22), "UTF-8")).replaceAll(" ", "+");
            //图表图片
            createPictureInExcel(dataChartCostStr, patriarch, wb, (short) 1, 27, (short) 22, 53);
        }
        if (!StringUtils.isEmpty(map.get("dataChartThree"))){
            String dataChartThree = map.get("dataChartThree");
            String dataChartCostStr = (URLDecoder.decode(dataChartThree.substring(22), "UTF-8")).replaceAll(" ", "+");
            //图表图片
            createPictureInExcel(dataChartCostStr, patriarch, wb, (short) 1, 55, (short) 22, 81);
        }
        if(!StringUtils.isEmpty(map.get("dataChartFour"))){
            String dataChartFour = map.get("dataChartFour");
            String dataChartCostStr = (URLDecoder.decode(dataChartFour.substring(22), "UTF-8")).replaceAll(" ", "+");
            //图表图片
            createPictureInExcel(dataChartCostStr, patriarch, wb, (short) 1, 83, (short) 22, 109);
        }

        //图片写入Excel
        OutputStream out = response.getOutputStream();
        response.setContentType("application/OCTET-STREAM;charset=UTF-8");
        response.setHeader("Content-Disposition", "attachment;filename=" + new String("echarts.xls".getBytes("gb2312"), "ISO8859-1"));
        wb.write(out);
        wb.close();
    }
    @SuppressWarnings("restriction")
    public  static void createPictureInExcel(String dataChart, HSSFPatriarch patriarch, HSSFWorkbook wb,
                                             short col1, int row1, short col2, int row2) throws Exception {
        //用于将BASE64编码格式转为byte数组
        //BASE64Decoder base64Decoder = new BASE64Decoder();
        ByteArrayOutputStream dataChartoutStream = new ByteArrayOutputStream();
        //将dataChartStringin作为输入流，读取图片存入image中
        //ByteArrayInputStream dataChartin = new ByteArrayInputStream(base64Decoder.decodeBuffer(dataChart));
        //BufferedImage dataChartbufferImg = ImageIO.read(dataChartin);
        //利用HSSFPatriarch将图片写入EXCEL
        //ImageIO.write(dataChartbufferImg, "png", dataChartoutStream);
        HSSFClientAnchor anchorCostStr = new HSSFClientAnchor(0, 0, 0, 0, col1, row1, col2, row2);
        //画图
        patriarch.createPicture(anchorCostStr, wb.addPicture(dataChartoutStream.toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG));
    }
}
